// 数据库连接和基础操作模块
require('dotenv').config()

const { createClient } = require('@supabase/supabase-js')

// Supabase 配置
const supabaseUrl = process.env.SUPABASE_URL
const supabaseServiceKey = process.env.SUPABASE_SERVICE_ROLE_KEY

if (!supabaseServiceKey) {
  console.error('❌ 错误: 未设置 SUPABASE_SERVICE_ROLE_KEY 环境变量')
  console.log('💡 请在 .env 文件中设置:')
  console.log('   SUPABASE_SERVICE_ROLE_KEY=your-service-role-key')
  process.exit(1)
}

const supabase = createClient(supabaseUrl, supabaseServiceKey, {
  auth: {
    autoRefreshToken: false,
    persistSession: false
  }
})

// 数据库操作工具函数
class DatabaseManager {
  constructor() {
    this.supabase = supabase
  }

  // 检查连接
  async testConnection() {
    try {
      const { data, error } = await this.supabase
        .from('movies')
        .select('count')
        .limit(1)
      
      if (error) {
        throw error
      }
      
      return { success: true, message: '数据库连接成功' }
    } catch (error) {
      return { success: false, error: error.message }
    }
  }

  // 检查表是否存在数据
  async checkTableData(tableName) {
    try {
      const { data, error } = await this.supabase
        .from(tableName)
        .select('id')
        .limit(1)
      
      if (error) {
        throw error
      }
      
      return { exists: data.length > 0, count: data.length }
    } catch (error) {
      return { exists: false, error: error.message }
    }
  }

  // 获取表数据数量
  async getTableCount(tableName) {
    try {
      const { count, error } = await this.supabase
        .from(tableName)
        .select('*', { count: 'exact', head: true })
      
      if (error) {
        throw error
      }
      
      return { success: true, count: count || 0 }
    } catch (error) {
      return { success: false, error: error.message }
    }
  }

  // 清空表数据
  async clearTable(tableName) {
    try {
      const { error } = await this.supabase
        .from(tableName)
        .delete()
        .neq('id', 0) // 删除所有记录
      
      if (error) {
        throw error
      }
      
      return { success: true, message: `${tableName} 表已清空` }
    } catch (error) {
      return { success: false, error: error.message }
    }
  }

  // 批量插入数据
  async batchInsert(tableName, data) {
    try {
      const { data: insertedData, error } = await this.supabase
        .from(tableName)
        .insert(data)
        .select()
      
      if (error) {
        throw error
      }
      
      return { success: true, data: insertedData, count: insertedData.length }
    } catch (error) {
      return { success: false, error: error.message }
    }
  }

  // 单条插入数据
  async insertOne(tableName, data) {
    try {
      const { data: insertedData, error } = await this.supabase
        .from(tableName)
        .insert(data)
        .select()
        .single()
      
      if (error) {
        throw error
      }
      
      return { success: true, data: insertedData }
    } catch (error) {
      return { success: false, error: error.message }
    }
  }

  // 更新数据
  async update(tableName, data, condition) {
    try {
      let query = this.supabase
        .from(tableName)
        .update(data)
      
      // 添加条件
      Object.keys(condition).forEach(key => {
        query = query.eq(key, condition[key])
      })
      
      const { data: updatedData, error } = await query.select()
      
      if (error) {
        throw error
      }
      
      return { success: true, data: updatedData }
    } catch (error) {
      return { success: false, error: error.message }
    }
  }

  // 查询数据
  async query(tableName, options = {}) {
    try {
      let query = this.supabase.from(tableName).select(options.select || '*')
      
      // 添加条件
      if (options.where) {
        Object.keys(options.where).forEach(key => {
          query = query.eq(key, options.where[key])
        })
      }
      
      // 添加排序
      if (options.orderBy) {
        query = query.order(options.orderBy.column, { ascending: options.orderBy.ascending !== false })
      }
      
      // 添加限制
      if (options.limit) {
        query = query.limit(options.limit)
      }
      
      const { data, error } = await query
      
      if (error) {
        throw error
      }
      
      return { success: true, data }
    } catch (error) {
      return { success: false, error: error.message }
    }
  }

  // 检查记录是否存在
  async exists(tableName, condition) {
    try {
      let query = this.supabase
        .from(tableName)
        .select('id')
        .limit(1)
      
      Object.keys(condition).forEach(key => {
        query = query.eq(key, condition[key])
      })
      
      const { data, error } = await query
      
      if (error) {
        throw error
      }
      
      return { exists: data.length > 0, data: data[0] || null }
    } catch (error) {
      return { exists: false, error: error.message }
    }
  }
}

module.exports = {
  supabase,
  DatabaseManager
}
